package com.example.sj12;

import android.util.Log;
import static android.content.ContentValues.TAG;
import java.sql.ResultSet;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class UserDao {

    JdbcUtil jdbcUtil = JdbcUtil.getInstance();
    //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
    Connection conn = jdbcUtil.getConnection("su","root","123456");
    //注册
    public  boolean register(String username,String password,boolean c){
        if (conn==null){
            Log.i(TAG,"register:conn is null");
            return false;
        }else if(c){
            //进行数据库操作
            String sql = "insert into user(username,password,url) values(?,?,?)";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,username);
                pre.setString(2,password);
                pre.setString(3,"http://47.112.156.101/ctx/tx.png");
                return pre.execute();
            } catch (SQLException e) {
                System.out.println("插入失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        else{
            //进行数据库操作
            String sql = "insert into sj(username,password,url) values(?,?,?)";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,username);
                pre.setString(2,password);
                pre.setString(3,"http://47.112.156.101/stx/tx.png");
                return pre.execute();
            } catch (SQLException e) {
                System.out.println("插入失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    //检验用户是否存在
    public boolean check(String username,boolean c){
        if (conn==null){
            Log.i(TAG,"check:conn is null");
            return false;
        }else if(c){
            String sql = "select * from user where username=?";
            try {
                PreparedStatement pres = conn.prepareStatement(sql);
                pres.setString(1,username);
                //pres.setString(2,password);
                ResultSet res = pres.executeQuery();
                boolean t = res.next();
                return t;
            } catch (SQLException e) {
                System.out.println("查询失败"+e);
                return false;
            }

        }
        else {
            String sql = "select * from sj where username=?";
            try {
                PreparedStatement pres = conn.prepareStatement(sql);
                pres.setString(1,username);
                //pres.setString(2,password);
                ResultSet res = pres.executeQuery();
                boolean t = res.next();
                return t;
            } catch (SQLException e) {
                System.out.println("查询失败"+e);
                return false;
            }
        }
    }
    //登录
    public boolean login(String username,String password){
        if (conn==null){
            Log.i(TAG,"login:conn is null");
            return false;
        }else {
            String sql = "select * from sj where username=? and password=?";
            try {
                PreparedStatement pres = conn.prepareStatement(sql);
                pres.setString(1,username);
                pres.setString(2,password);
                ResultSet res = pres.executeQuery();
                boolean t = res.next();
                return t;
            } catch (SQLException e) {
                System.out.println("登陆失败"+e);
                return false;
            }

        }
    }
    public List<Data> getDate(String s) {
        //JdbcUtil jdbcUtil = JdbcUtil.getInstance();
        //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
        LinkedList<Data> list= new LinkedList<>();// //结果存放集he
        //Connection conn = jdbcUtil.getConnection("su", "root", "123456");
        try {
            String sql = "select * from house where housename like ? ";//where username=?
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,"%"+s+"%");//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            //Data d=new Data();
                            //d.setUsername(rs.getString("username"));
                            //System.out.println(rs.getString("username"));
                            list.add(new Data(rs.getString("housename")));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询失败"+e);
            //e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("查询失败" + e);
            }
        }
        return list;
    }
    public Data insert(String a) {
        Data get=new Data();
        try {
            //进行数据库操作
            String sql = "select * from house where housename=?";
            //pre.setString(1,username);//为sql语句中的第一个？参数设置string的值username
            //pre.setString(2,password);
            //return pre.execute();
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,a);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Data get=new Data();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            get.setUrl(rs.getString("url"));
                            get.setName(rs.getString("name"));
                            get.setPrice(rs.getString("price"));
                            get.setType(rs.getString("type"));
                            get.setTel(rs.getString("tel"));
                            get.setIdcard(rs.getString("idcard"));
                            get.setArea(rs.getString("area"));
                            get.setToward(rs.getString("toward"));
                            get.setIntroduce(rs.getString("introduce"));
                            get.setHousename(rs.getString("housename"));
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), "http://"+rs.getString("url"),count));
                        }
                        return get;
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("插入失败"+e);
            return null;
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return get;
    }
    public List<Data> getData() {//从数据库选择数据加载到HomeFragme界面
        //JdbcUtil jdbcUtil = JdbcUtil.getInstance();
        //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
        LinkedList<Data> list= new LinkedList<>();// //结果存放集he
        //Connection conn = jdbcUtil.getConnection("su", "root", "123456");
        try {
            String sql = "select housename,introduce,url,name from house";//where username=?
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                //ps.setString(1, "N");//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            list.add(new Data(rs.getString("housename"),rs.getString("introduce"), "http://"+rs.getString("url"),rs.getString("name")));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询失败1"+e);
            //e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("查询失败" + e);
            }
        }
        return list;
    }

    public int yuyue(String username, String n, String usertel, String t, String hostname, String housename) {
        if (conn==null){
            Log.i(TAG,"yuyue:conn is null");
            return 0;
        } else{
            //进行数据库操作
            //String sql = "insert into user(realname,tel,timeinto,househost,housename) values(?,?,?,?,?) where username == ?";
            //String sql ="if exists (select * from user where username=?)INSERT INTO user(realname,tel,timeinto,househost,housename) values(?,?,?,?,?)";
            //String sql ="update user set realname=?,tel=?,timeinto=?,househost=?,housename=? where username=?";
            String sql = "insert into po(realname,tel,timeinto,househost,housename,customer) values(?,?,?,?,?,?)";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,n);
                pre.setString(2,usertel);
                pre.setString(3,t);
                pre.setString(4,hostname);
                pre.setString(5,housename);
                pre.setString(6,username);
                return pre.executeUpdate();
            } catch (SQLException e) {
                System.out.println("插入失败"+e);
                return 0;
            }
            finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public List<Data> searchData(String username) {
        //JdbcUtil jdbcUtil = JdbcUtil.getInstance();
        //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
        LinkedList<Data> list= new LinkedList<>();// //结果存放集he
        //Connection conn = jdbcUtil.getConnection("su", "root", "123456");
        try {
            //String sql = "select * from house where housename in (select housename from po where customer = ?) ";//where username=?
            String sql = "select * from po where househost = ?";//where username=?
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,username);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            //Data d=new Data();
                            //d.setUsername(rs.getString("username"));
                            //System.out.println(rs.getString("username"));
                            list.add(new Data(rs.getString("housename"),rs.getString("customer"),rs.getString("realname"),rs.getString("tel"),rs.getString("timeinto"),rs.getString("curl")));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询失败"+e);
            //e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("查询失败" + e);
            }
        }
        return list;
    }
    //检验用户是否入住
    public boolean checkinsert(String housename,String username){
        if (conn==null){
            Log.i(TAG,"checkinsert:conn is null");
            return false;
        } else {
            String sql = "select * from po where housename=? and customer=?";
            try {
                PreparedStatement pres = conn.prepareStatement(sql);
                pres.setString(1,housename);
                pres.setString(2,username);
                //pres.setString(2,password);
                ResultSet res = pres.executeQuery();
                boolean t = res.next();
                return t;
            } catch (SQLException e) {
                System.out.println("查询失败"+e);
                return false;
            }
        }
    }

    public void comment(String housename, String househost, String username, String comment, String date,String url,float rating) {
        if (conn==null){
            Log.i(TAG,"comment:conn is null");
        } else{
            //进行数据库操作
            String sql = "insert into comment(housename,househost,customer,comment,timeinto,curl,rating) values(?,?,?,?,?,?,?)";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,housename);
                pre.setString(2,househost);
                pre.setString(3,username);
                pre.setString(4,comment);
                pre.setString(5,date);
                pre.setString(6,url);
                pre.execute();
            } catch (SQLException e) {
                System.out.println("插入失败"+e);
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
//获取用户评论
    public List<Data> getComment(String housename) {
        //JdbcUtil jdbcUtil = JdbcUtil.getInstance();
        //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
        LinkedList<Data> list= new LinkedList<>();// //结果存放集he
        //Connection conn = jdbcUtil.getConnection("su", "root", "123456");
        try {
            String sql = "select househost,timeinto,comment,customer,comment_id,curl,rating from comment where housename=?";//where username=?
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, housename);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            list.add(new Data(rs.getString("househost"),rs.getString("timeinto"), rs.getString("comment"), rs.getString("customer"),rs.getString("comment_id"), rs.getString("curl"), rs.getFloat("rating")));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询失败1"+e);
            //e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("查询失败" + e);
            }
        }
        return list;
    }
//退房caozuo
    public Boolean Refund(String info1, String info2) {
        if (conn==null){
            Log.i(TAG,"register:conn is null");
            return false;
        } else{
            //进行数据库操作
            String sql = "delete from po where housename =? and customer =?";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,info1);
                pre.setString(2,info2);
                return pre.execute();
            } catch (SQLException e) {
                System.out.println("删除失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public Data GetUser(String username) {
        Data get=new Data();
        try {
            //进行数据库操作
            String sql = "select * from user where username=?";
            //pre.setString(1,username);//为sql语句中的第一个？参数设置string的值username
            //pre.setString(2,password);
            //return pre.execute();
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,username);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Data get=new Data();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            get.setUrl(rs.getString("url"));
                            get.setUsername(rs.getString("username"));
                            get.setIntroduce(rs.getString("introduce"));
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), "http://"+rs.getString("url"),count));
                        }
                        return get;
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("插入失败"+e);
            return null;
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                //e.printStackTrace();
                System.out.println("关闭失败"+e);
            }
        }
        return get;
    }

    public void Updatectx(String username) {
        if (conn==null){
            Log.i(TAG,"Updatectx:conn is null");
        } else{
            //进行数据库操作
            String sql = "update user set url =? where username =?";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,"http://47.112.156.101/ctx/image-"+username+".png");
                pre.setString(2,username);
                pre.execute();
            } catch (SQLException e) {
                System.out.println("更新失败"+e);
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public List<Data> searchHouse(String name) {
        //JdbcUtil jdbcUtil = JdbcUtil.getInstance();
        //第一个参数为数据库名称，第二个参数为数据库账号 第三个参数为数据库密码
        LinkedList<Data> list= new LinkedList<>();// //结果存放集he
        //Connection conn = jdbcUtil.getConnection("su", "root", "123456");
        try {
            String sql = "select * from house where name=?";//where username=?
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,name);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            //Data d=new Data();
                            //d.setUsername(rs.getString("username"));
                            //System.out.println(rs.getString("username"));
                            list.add(new Data(rs.getString("housename"),rs.getString("introduce"), "http://"+rs.getString("url"),rs.getString("name")));
                        }
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("查询失败"+e);
            //e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println("查询失败" + e);
            }
        }
        return list;
    }

    public  boolean insert(String a,String b,String c,String d,String e1,String h,String i,String j,String k,int p){
        if (conn==null){
            Log.i(TAG,"insert:conn is null");
            return false;
        }else {
            //进行数据库操作
            String sql = "insert into house(url,name,housename,idcard,tel,price,introduce,type,area,toward,picnumber) values(?,?,?,?,?,?,?,?,?,?,?)";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,"47.112.156.101/stx/image-"+a+"-"+p+".png");
                pre.setString(2,a);
                pre.setString(3,b);
                pre.setString(4,c);
                pre.setString(5,d);
                pre.setString(6,e1);
                //pre.setString(7,f);
               // pre.setString(8,g);
                pre.setString(7,h);
                pre.setString(8,i);
                pre.setString(9,j);
                pre.setString(10,k);
                pre.setInt(11,p);
                return pre.execute();
            } catch (SQLException e) {
                System.out.println("插入失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public Data GetSUser(String username) {
        Data get=new Data();
        try {
            //进行数据库操作
            String sql = "select * from sj where username=?";
            //pre.setString(1,username);//为sql语句中的第一个？参数设置string的值username
            //pre.setString(2,password);
            //return pre.execute();
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,username);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Data get=new Data();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            get.setUrl(rs.getString("url"));
                            get.setUsername(rs.getString("username"));
                            get.setIntroduce(rs.getString("introduce"));
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), "http://"+rs.getString("url"),count));
                        }
                        return get;
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("插入失败"+e);
            return null;
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                //e.printStackTrace();
                System.out.println("关闭失败"+e);
            }
        }
        return get;
    }

    public Data GetPicnumber(String susername) {
        Data get=new Data();
        try {
            //进行数据库操作
            String sql = "select max(picnumber) from house where name=?";//选取图片id最大值
            //pre.setString(1,username);//为sql语句中的第一个？参数设置string的值username
            //pre.setString(2,password);
            //return pre.execute();
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,susername);//(s)
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Data get=new Data();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            get.setPicnumber(rs.getInt("max(picnumber)"));
                            /*get.setUrl(rs.getString("url"));
                            get.setUsername(rs.getString("username"));
                            get.setIntroduce(rs.getString("introduce"));*/
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), "http://"+rs.getString("url"),count));
                        }
                        return get;
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("插入失败"+e);
            return null;
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                //e.printStackTrace();
                System.out.println("关闭失败"+e);
            }
        }
        return get;

    }

    public Data yuyueinfo(String customer,String househost) {
        Data get=new Data();
        try {
            //进行数据库操作
            String sql = "select * from po where customer=? and housename=?";
            //pre.setString(1,username);//为sql语句中的第一个？参数设置string的值username
            //pre.setString(2,password);
            //return pre.execute();
            if (conn != null) {
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1,customer);//(s)
                ps.setString(2,househost);
                if (ps != null){
                    // ps.setString(1,id);
                    ResultSet rs = ps.executeQuery();
                    if (rs != null){
                        int count = rs.getMetaData().getColumnCount();
                        //Data get=new Data();
                        Log.e("prx","列总数：" + count);
                        while (rs.next()){
                            get.setRealname(rs.getString("realname"));
                            get.setTel(rs.getString("tel"));
                            get.setTimeinto(rs.getString("timeinto"));
                            get.setHousename(rs.getString("housename"));
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), Glide.with(mContext).load(rs.getString("url")));
                            //Glide.with(mContext).load(mData.get(position).getUrl()).into(img_icon);
                            //list.add(new Data(rs.getString("name"),rs.getString("introduce"), "http://"+rs.getString("url"),count));
                        }
                        return get;
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("插入失败"+e);
            return null;
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                //e.printStackTrace();
                System.out.println("关闭失败"+e);
            }
        }
        return get;
    }

    public boolean UpdateHouse(String username, String housename, String housename1, String idcard, String tel, String price, String type, String area, String toward, String introduce) {
        if (conn==null){
            Log.i(TAG,"UpdateHouse:conn is null");
            return false;
        }else {
            //进行数据库操作
            String sql = "update house set housename =?,idcard=?,tel=?,price=?,type=?,area=?,toward=?,introduce=? where name =? and housename=?";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,housename1);
                pre.setString(2,idcard);
                pre.setString(3,tel);
                pre.setString(4,price);
                pre.setString(5,type);
                pre.setString(6,area);
                pre.setString(7,toward);
                pre.setString(8,introduce);
                pre.setString(9,username);
                pre.setString(10,housename);
                return pre.execute();
            } catch (SQLException e) {
                System.out.println("更新失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public Boolean delete(String info1) {
        if (conn==null){
            Log.i(TAG,"register:conn is null");
            return false;
        } else{
            //进行数据库操作
            String sql = "delete from house where housename =?";
            try {
                PreparedStatement pre = conn.prepareStatement(sql);
                pre.setString(1,info1);
                //return pre.execute();
                boolean res = pre.execute();
                return res;
            } catch (SQLException e) {
                System.out.println("删除失败"+e);
                return false;
            }finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
